package java2021_9_23;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 *         ResultSet resultSet = statement.executeQuery();
 *         //5.遍历结果集合
 *         // 先取出结果中的每一行,再取出每一行中需要的列饥渴.
 *         //这个遍历的代码,非常像,迭代器遍历
 *         while(resultSet.next()){//类似于i++,如果遍历到最后了next就返回false,循环就结束了
 *             //通过 resultSet.next() 获取当前行
 *             //再来取每一列
 *             int id = resultSet.getInt("id");
 *             String name = resultSet.getString("name");
 *             System.out.println(id + "\t"+name);
 *         }
 */
//创建BookDao类来封装针对Book表的增删改查
public class BookDao {
    //1.新增书籍
    public boolean add(Book book){
        //把book对象插入到数据库的book表中
        Connection connection = null;
        PreparedStatement statement = null;
        try{
//            1.和数据库建立连接
            //            connection = statement.getConnection();这样子不行?
//            答案是不行,因为DataSource实例你都没创建,我们通过DButil这个类来对其进行封装,使得其只用实例化一次,只用就不用每次都是实例化了.
//            且还可以根据需求对DButil里调整为懒汉模式,或者是饿汉模式
              connection = DButil.getConnection();
              //2.拼装SQL语句
            /**
             *         //以插入数据为例,往student表中插入数据
             *         String sql = "insert into student values(1,'张三')";//静态的sql
             *        当前这个sql只是一个单纯的字符串,要想让mysql服务区能识别,还需要把这个字符串转成一个专门的对象
             *         PreparedStatement statement = connection.prepareStatement(sql);
             *        PreparedStatement对象,能够帮助我们动态的构造出sql,同时也进行一些校验检查
             */
            String sql = "insert into book values(null,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            //?表示占位符,还需要通过代码来替换掉
            //1表示第一个?,以此类推
            statement.setString(1,book.getName());
            statement.setString(2,book.getAuthor());
            statement.setInt(3,book.getPrice());
            statement.setString(4,book.getType());
            statement.setInt(5,book.isBorrowed()?1:0);
//            3.执行SQL
            //        statement提供了两个系列的execute
            //一个是excuteUpdate,用于增加,删除,修改   => 返回值类型为int,表示影响到了多少行
            //一个是excuteQuery,用于进行查找,        => ResultSet 表示返回的结果集(相当于一个表格)
            int ret = statement.executeUpdate();
            if(ret != 1){
                //如果插入失败,返回false;
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //在这一定会被执行,用于释放资源
//            释放资源的顺序，要和申请的资源的顺序相反
            DButil.close(connection,statement,null);
        }
        return false;
    }
    //2.查看所有书籍
    public List<Book> selectAll(){
        List<Book> ret = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1.连接数据库
            connection = DButil.getConnection();
            //2.拼装sql语句
            String sql = "select*from book";
            statement = connection.prepareStatement(sql);
            //3.执行sql
            resultSet = statement.executeQuery();
            //4.遍历结果集合
            while(resultSet.next()){
                //通过 resultSet.next() 获取当前行
                //再来取每一列
                Book book = new Book();
                book.setBookId(resultSet.getInt("bookId"));
                book.setName(resultSet.getString("name"));
                book.setAuthor(resultSet.getString("author"));
                book.setPrice(resultSet.getInt("price"));
                book.setType(resultSet.getString("type"));
                book.setBorrowed(resultSet.getInt("isBorrowed") == 1);
                ret.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //手动释放资源
            DButil.close(connection,statement,resultSet);
        }
        return ret;
    }
    //3.根据名字找书籍
    public List<Book> selectBook(String name){
        List<Book> books = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //1.和数据库建立连接
            connection = DButil.getConnection();
            //2.构造sql语句
            String sql = "select*from book where name = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            //3.执行sql
            resultSet = statement.executeQuery();//接受执行结果的返回值---一个表
            //4.遍历结果集合
            while(resultSet.next()){
                //一行一行读取
                String Name = resultSet.getString("name");
//                if(Name.equals(name)){
                    Book book = new Book();
                    book.setBookId(resultSet.getInt("bookId"));
                    book.setName(resultSet.getString("name"));
                    book.setAuthor(resultSet.getString("author"));
                    book.setPrice(resultSet.getInt("price"));
                    book.setType(resultSet.getString("type"));
                    book.setBorrowed(resultSet.getInt("isBorrowed") == 1);
                    books.add(book);
//                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            DButil.close(connection,statement,resultSet);
        }
        return books;
    }
    //4.删除书籍
    public boolean deleteBook(int bookId){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1.与数据库建立连接
            connection = DButil.getConnection();
            //2.构造sql
            String sql = "delete from book where bookId = ?";
            statement = connection.prepareStatement(sql);
//        PreparedStatement对象,能够帮助我们动态的构造出sql,同时也进行一些校验检查
            statement.setInt(1,bookId);
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret != 1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DButil.close(connection,statement,null);
        }
        return false;
    }
    //5.借书
    public boolean borrowBook(int bookId){
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        //这些只是个引用,左边引用,右对象.可得记好咯
        try {
            //1.与数据库建立连接
            connection = DButil.getConnection();
            //2.构造sql语句
            //查看书籍是否存在,或者是否被借出
            String sql = "select*from book where bookId = ?";
            statement1 = connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            //3.执行sql
            resultSet = statement1.executeQuery();
            //4.遍历返回的结果集 -- 要么一条,要么零条,因为这是根据主键来进行查找
            if (resultSet.next()){
                boolean isBorrow = (resultSet.getInt("isBorrowed") == 1);
                if(isBorrow){
                    System.out.println("很抱歉!该书籍已经被借走! bookId = "+bookId);
                    return false;
                }
            }else{
                System.out.println("书不存在!无法借出!bookId = " + bookId);
                return false;
            }
            //走到这,说明书是存在,且还未被借出.接下来就可以实现借书操作了
            //5.拼装sql语句
//            String sql2 = "update book set isBorrow = 1 where bookId = ?";
//            statement = connection.prepareStatement(sql2);
            sql = "update book set isBorrowed = 1 where bookId = ?";

            statement2 = connection.prepareStatement(sql);//问题1：这里又一次创建了statement对象,如果前面的statement对象没有主动关闭,那只能靠垃圾回收
            //这个操作就会导致之前的statement引用中持有的对象,就没有引用指向了.就不能手动关闭了,其实也还好GC在释放这个对象的时候也会处理关闭逻辑.
//            但这样终究是不好的，最稳妥的做法还是手动去关闭。
//            就需要把当前的两个PrepareStatement对象都记录下来，分别进行手动释放
//问题2,和多线程相关
            statement2.setInt(1,bookId);
            //6.执行sql
            int ret = statement2.executeUpdate();
            if(ret != 1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            DButil.close(connection,statement,resultSet);//这里只把三个对象释放了一次
////            而statment的对象创建了两次

            //注意释放的顺序!
            if(resultSet != null){
                    try {
                        resultSet.close();
                    }catch (SQLException e){
                        e.printStackTrace();
                    }
            }
            if(statement2 != null){
                try {
                        statement2.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement1 != null){
                try {
                    statement1.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }

        }
        return false;//如果前面任何一部出现了异常,触发catch了.那么都会返回一个false
    }
    //6.还书
    public boolean returnBook(int bookId){
        Connection connection = null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        ResultSet resultSet = null;
        try {
            //1.与数据库进行连接
            connection = DButil.getConnection();
            //2.构造sql
            String sql = "select*from book where bookId = ?";
            statement1 = connection.prepareStatement(sql);
            statement1.setInt(1,bookId);
            //3.执行sql
            //看看书库里有无此书信息
            resultSet = statement1.executeQuery();
            if(resultSet.next()){
                boolean isBorrow = (resultSet.getInt("isBorrowed") == 1);
                if(!isBorrow){
                    System.out.println("书没有被借出,不用还!bookId = "+bookId);
                    return false;
                }
            }else{
                System.out.println("没有此书信息!bookId = " + bookId);
                return false;
            }
            //进行还书操作
            //4.构造sql
            sql = "update book set isBorrowed = 0 where bookId = ?";
            statement2 = connection.prepareStatement(sql);
            statement2.setInt(1,bookId);
            int ret = statement2.executeUpdate();
            if(ret != 1){
                return false;
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(resultSet != null){
                try {
                    resultSet.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement2 != null){
                try {
                    statement2.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement1 != null){
                try {
                    statement1.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }

        }
        return false;
    }
}
